﻿Public Class 查看库存

    Dim RH As Integer
    Dim CS As Integer

    '关闭窗体
    Private Sub 查看库存_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        SearchForm = Nothing
    End Sub

    '窗体加载
    Private Sub 查看库存_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        ComboBox1.Items.Add("所有品牌")

        '加载品牌
        Dim sqlstr As String = "select 品牌 from 品牌表"
        Dim ds As Data.DataSet = SQLtoDataSet(sqlstr)
        Dim ds_table As Data.DataTable = ds.Tables(0)
        If ds_table.Rows.Count > 0 Then
            For i = 0 To ds_table.Rows.Count - 1
                ComboBox1.Items.Add(ds_table.Rows(i).Item(0).ToString)
            Next
        End If

        Label6.Text = ""
        Label7.Text = ""
        Label9.Text = ""

        ComboBox1.Focus()

    End Sub

    '选择品牌
    Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged

        If Trim(ComboBox1.Text) = "" Then
            'MsgBox("请选择商品的品牌！")
            'ComboBox1.Focus()
            Label6.Text = ""
            Label7.Text = ""
            Exit Sub
        End If

        If Trim(ComboBox1.Text) = "所有品牌" Then
            'MsgBox("请选择商品的品牌！")
            'ComboBox1.Focus()
            Label6.Text = ""
            Label7.Text = ""
            TextBox1.Text = ""
            ComboBox2.Enabled = False

            '显示所有品牌及型号库存
            Dim strtem As String = "select 品牌 from 品牌表"
            Dim dstemp As Data.DataSet = SQLtoDataSet(strtem)
            Dim dstabl As Data.DataTable = dstemp.Tables(0)
            If dstabl.Rows.Count > 0 Then
                DataGridView1.Rows.Clear()

                '逐品牌
                For i = 0 To dstabl.Rows.Count - 1
                    Dim PP As String = dstabl.Rows(i).Item(0).ToString

                    '搜型号
                    Dim strtem2 As String = "select 型号 from 型号表 where 所属品牌 = '" + PP + "'"
                    Dim dstemp2 As Data.DataSet = SQLtoDataSet(strtem2)
                    Dim dstabl2 As Data.DataTable = dstemp2.Tables(0)
                    If dstabl2.Rows.Count > 0 Then
                        '逐型号
                        For j = 0 To dstabl2.Rows.Count - 1
                            searchXH(PP, dstabl2.Rows(j).Item(0).ToString)
                        Next
                    End If

                Next
            End If

            Exit Sub
        End If


        '加载品牌
        ComboBox2.Enabled = True
        Dim sqlstr As String = "select 型号 from 型号表 where 所属品牌 ='" + ComboBox1.Text + "'"
        Dim ds As Data.DataSet = SQLtoDataSet(sqlstr)
        Dim ds_table As Data.DataTable = ds.Tables(0)
        If ds_table.Rows.Count > 0 Then
            ComboBox2.Items.Clear()
            DataGridView1.Rows.Clear()
            For i = 0 To ds_table.Rows.Count - 1
                ComboBox2.Items.Add(ds_table.Rows(i).Item(0).ToString)
                searchXH(ComboBox1.Text, ds_table.Rows(i).Item(0).ToString)
            Next
        End If

    End Sub
    Private Sub ComboBox1_GotFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox1.GotFocus
        ComboBox1_SelectedIndexChanged(sender, e)
    End Sub

    '选择型号
    Private Sub ComboBox2_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox2.SelectedIndexChanged

        If Trim(ComboBox2.Text) = "" Then
            'MsgBox("请选择商品的品牌！")
            'ComboBox2.Focus()
            Label6.Text = ""
            Label7.Text = ""
            Exit Sub
        End If

        '加载品牌
        Dim sqlstr As String = "select * from 型号表 where 所属品牌 ='" + ComboBox1.Text + "' and 型号 ='" + ComboBox2.Text + "'"
        Dim ds As Data.DataSet = SQLtoDataSet(sqlstr)
        Dim ds_table As Data.DataTable = ds.Tables(0)
        If ds_table.Rows.Count > 0 Then
            TextBox1.Text = ds_table.Rows(0).Item("备注").ToString
            searchKC(ComboBox1.Text, ComboBox2.Text)
            Label6.Text = RH.ToString
            Label7.Text = CS.ToString
            Label9.Text = (RH - CS).ToString + "台"
        End If

    End Sub
    Private Sub ComboBox2_GotFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox2.GotFocus
        ComboBox2_SelectedIndexChanged(sender, e)
    End Sub

    '查库存
    Public Sub searchKC(ByVal PP As String, ByVal XH As String)

        RH = 0
        CS = 0

        '计算总入货量
        Dim sqlstr1 As String = "select 品牌,型号,数量,备注2 from 入货表 where 品牌='" + PP + "' and 型号 ='" + XH + "' and 备注2 is null "
        Dim ds1 As Data.DataTable = SQLtoDataSet(sqlstr1).Tables(0)
        If ds1.Rows.Count > 0 Then
            For i = 0 To ds1.Rows.Count - 1
                RH += ds1.Rows(i).Item("数量")
                Debug.Print("备注2" + ds1.Rows(i).Item("备注2").ToString)
            Next
        End If


        '计算总出售量
        Dim sqlstr2 As String = "select 品牌,型号,售出数量,备注2 from 出售表 where 品牌='" + PP + "' and 型号 ='" + XH + "' and 备注2 is null "
        Dim ds2 As Data.DataTable = SQLtoDataSet(sqlstr2).Tables(0)
        If ds2.Rows.Count > 0 Then
            For i = 0 To ds2.Rows.Count - 1
                CS += ds2.Rows(i).Item("售出数量")
            Next
        End If

    End Sub

    '显示入货价
    Private Sub CheckBox1_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CheckBox1.CheckedChanged
        If CheckBox1.Checked Then
            DataGridView1.Columns("入货价").Visible = True
        Else
            DataGridView1.Columns("入货价").Visible = False
        End If
    End Sub

    '型号对应的
    Private Sub searchXH(ByVal PP As String, ByVal XH As String)
        '变量声明
        Dim RHL As Integer = 0
        Dim RHJ As String = ""
        Dim CHL As Integer = 0
        Dim CHJ As String = ""

        '入货
        Dim sqlstr As String = "select * from 入货表 where 品牌 ='" + PP + "' and 型号 = '" + XH + "'"
        Dim ds As Data.DataSet = SQLtoDataSet(sqlstr)
        Dim ds_table As Data.DataTable = ds.Tables(0)
        If ds_table.Rows.Count > 0 Then
            '入货价
            RHJ = ds_table.Rows(0).Item("入货单价").ToString

            '入货量
            For i = 0 To ds_table.Rows.Count - 1
                RHL += ds_table.Rows(i).Item("数量")
            Next
        End If

        '出售
        Dim sqlstr2 As String = "select * from 出售表 where 品牌 ='" + PP + "' and 型号 = '" + XH + "'"
        Dim ds2 As Data.DataSet = SQLtoDataSet(sqlstr2)
        Dim ds_table2 As Data.DataTable = ds2.Tables(0)
        If ds_table2.Rows.Count > 0 Then
            '入货量
            For i = 0 To ds_table2.Rows.Count - 1
                CHL += ds_table2.Rows(i).Item("售出数量")
            Next
        End If

        '售价
        Dim sqlstr3 As String = "select 一般售价 from 型号表 where 所属品牌 ='" + PP + "' and 型号 = '" + XH + "'"
        Dim ds3 As Data.DataSet = SQLtoDataSet(sqlstr3)
        Dim ds_table3 As Data.DataTable = ds3.Tables(0)
        If ds_table3.Rows.Count > 0 Then
            '出售价
            CHJ = ds_table3.Rows(0).Item(0).ToString
        End If

        '库存
        Dim KC As String = RHL - CHL

        '添加
        Dim row1() As String = {PP, XH, RHJ, CHJ, RHL, CHL, KC}
        DataGridView1.Rows.Add(row1)

    End Sub

End Class